package ufrj.devagil.timer;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class TimerdeAtividadesHelper extends SQLiteOpenHelper {

    private static final String KEY_NAME = "name";
    public static final String 	TABLE_TIMERS 		= "Timers";
    public static final String 	TABLE_HISTORY		= "history";
    private static final int 	DATABASE_VERSION 	= 2;
    private static final String DATABASE_NAME 		= "timer";
    private static final String CREATE_TABLE_TIMERS 	= "CREATE TABLE " + TABLE_TIMERS + " (_id INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_NAME + "VARCHAR(255) UNIQUE );";
    private static final String CREATE_TABLE_HISTORY 	= "CREATE TABLE " + TABLE_HISTORY + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, timerId INTEGER, tempo INTEGER, date INTEGER);";
    
    
    TimerdeAtividadesHelper(Context context) {
	super(context, DATABASE_NAME, null, DATABASE_VERSION);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
	db.execSQL(CREATE_TABLE_TIMERS);
	db.execSQL(CREATE_TABLE_HISTORY);
    }

    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	db.execSQL("DROP TABLE " + TABLE_TIMERS);
	db.execSQL("DROP TABLE " + TABLE_HISTORY);
	onCreate(db);
    }

    
    public void insertHistory(int timerId, long duration, long date){
	SQLiteDatabase db;
	ContentValues  values;

	db 	= getWritableDatabase();
	values  = new ContentValues();
	
	values.put("timerId", timerId);
	values.put("tempo", duration);
	values.put("date", date);
	
	db.insert(TimerdeAtividadesHelper.TABLE_HISTORY, null, values);
	db.close();
	
    }
    
    
    public ArrayList<Timer> getArrayTimers(Context context){
	ArrayList<Timer> timers;
	SQLiteDatabase   db;
	String 		 name;
	Cursor 		 c;
	int 		 indexName, indexId, indexTempoTotal, id, tempoTotal;
	
 	
	db        = getReadableDatabase();
	c 	  = db.query(TABLE_TIMERS, null, null, null, null, null, null);
	timers    = new ArrayList<Timer>();
	indexName = c.getColumnIndex("name");
	indexId   = c.getColumnIndex("_id");
	
 	while (c.moveToNext()){
	    name = c.getString(indexName);
	    id   = c.getInt(indexId);
	    timers.add (new Timer(id, name, context));
	}
 	c.close();
 	
 	c 		= db.rawQuery("SELECT timerId, SUM(tempo) AS tempoTotal FROM " + TABLE_HISTORY + " GROUP BY timerId", null);
 	indexTempoTotal = c.getColumnIndex("tempoTotal");
 	indexId		= c.getColumnIndex("timerId");
 	//not good implementation, buh for vew object is ok - Leandro
 	while (c.moveToNext()){
 	    id 		= c.getInt(indexId);
 	    tempoTotal  = c.getInt(indexTempoTotal); 
 	    
 	    for(int i=0; i<timers.size(); i++){
 		if (timers.get(i).id == id){
 		    timers.get(i).tempoTotal = tempoTotal;
 		    break;
 		}
 	    }
 	}
 	c.close();
 	db.close();
 	
 	return timers;
    }
    
    
    public Cursor getTimers(){
	SQLiteDatabase db = getReadableDatabase();
	return db.rawQuery("SELECT name, SUM(tempo) AS tempoTotal, Timers._id FROM Timers, history where Timers._id == timerId GROUP BY timerId", null);

    }
    
    
    public List<String> getTimersNames()
    {
	List<String> timerNames = new ArrayList<String>();
	
	String query = "SELECT name FROM Timers, history where Timers._id == timerId GROUP BY timerId";
	SQLiteDatabase db = this.getReadableDatabase();
	
	Cursor cursor = db.rawQuery(query, null);
	
	if(cursor.moveToFirst())
	{
	    do
	    {
		timerNames.add(cursor.getString(0));
	    }while(cursor.moveToNext());
	}
	cursor.close();
	db.close();
	return timerNames;
    }
    
    
    public void renomearTimer(String nomeAntigo, String novoNome)
    {
	SQLiteDatabase db = this.getWritableDatabase();
	
	ContentValues values = new ContentValues();
	values.put(KEY_NAME, novoNome);
	
	db.update(TABLE_TIMERS, values, KEY_NAME + "=?", new String[] { String.valueOf(nomeAntigo) });
	db.close();
    }
    
    public void deletarTimer(String nomeTimer)
    {
	SQLiteDatabase db = this.getWritableDatabase();
	db.delete(TABLE_TIMERS, KEY_NAME+"=?", new String[] { String.valueOf(nomeTimer) });
	db.close();
    }
    
    public int insertTimer(String nome){
	int		id;
	SQLiteDatabase 	db;
	ContentValues   values;
	
	db 	= getWritableDatabase();
	values 	= new ContentValues();
	
	values.put("name", nome);
	id = (int) db.insert(TABLE_TIMERS, null, values);
	db.close();
	
	insertHistory(id, 0, 0);
		
	return id;
    }
 }
